﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ProjetoBe.cs;
using System.Data;

namespace ProjetoDalc.cs
{
    public class CriancaDalc
    { 

        public List<string> buscarNomesCrianca(string data)
        {
            SqlDataReader reader;
            List<string> nomesCriancas = new List<string>();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "SELECT CRIANCA_NOME FROM CRIANCA WHERE CRIANCA_NASCIMENTO = @DataNascimento;";
                command.Parameters.AddWithValue("@DataNascimento", data);
                conexao.Open();

                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                        nomesCriancas.Add((reader.GetString(0)));
                }
                conexao.Close();
                return nomesCriancas;
            }
        }

        public CriancaBe cadastrarCrianca(CriancaBe objeto)
        {
            try
            {
                 using (SqlConnection conexao = Connection.CriarConexao())
                 {
                     SqlCommand command = new SqlCommand();
                     SqlDataAdapter adapter = new SqlDataAdapter();
                
                     command.Connection = conexao;
                     command.CommandText = "INSERT INTO CRIANCA VALUES(@NOME, @RESTRICOES,@NASCIMENTO, @FOTO, @COUNT, @COMBO, @STATUS) SELECT SCOPE_IDENTITY();";
                     command.Parameters.AddWithValue("NOME", objeto.nome);
                     command.Parameters.AddWithValue("RESTRICOES", objeto.restricoes);
                     command.Parameters.AddWithValue("NASCIMENTO", objeto.nascimento);
                     command.Parameters.AddWithValue("FOTO", "NULL");
                     command.Parameters.AddWithValue("COUNT", objeto.count);
                     command.Parameters.AddWithValue("COMBO", objeto.combo = "ATIVO");
                     command.Parameters.AddWithValue("STATUS", objeto.status = "ATIVO");

                     adapter.InsertCommand = command;
                     conexao.Open();
                     decimal id = (decimal)command.ExecuteScalar();
                     objeto.id = Convert.ToInt32(id);
                     conexao.Close();
                 }
            }
            catch{}
            return objeto;
        }

        public void alterarDadosCrianca(CriancaBe crianca)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "UPDATE CRIANCA SET CRIANCA_NOME = @nome, CRIANCA_RESTRICOES = @restricoes, CRIANCA_NASCIMENTO = @nascimento, CRIANCA_FOTO = @foto, CRIANCA_COUNT = @count WHERE CRIANCA_ID = @id";
                command.Parameters.AddWithValue("@id", crianca.id);
                command.Parameters.AddWithValue("@nome", crianca.nome);
                command.Parameters.AddWithValue("@restricoes", crianca.restricoes);
                command.Parameters.AddWithValue("@nascimento", crianca.nascimento);
                command.Parameters.AddWithValue("@foto", crianca.enderecoFoto);
                command.Parameters.AddWithValue("@count", crianca.count);

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        public CriancaBe buscarCriancaId(int id)
        {
            SqlDataReader reader;
            CriancaBe crianca = new CriancaBe();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT * FROM CRIANCA WHERE CRIANCA_ID = @id;";
                command.Parameters.AddWithValue("@id", id);
                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        crianca.id = Convert.ToInt32(reader["CRIANCA_ID"]);
                        crianca.nome = reader["CRIANCA_NOME"].ToString();
                        crianca.restricoes = reader["CRIANCA_RESTRICOES"].ToString();
                        crianca.nascimento = Convert.ToDateTime(reader["CRIANCA_NASCIMENTO"]);
                        crianca.enderecoFoto = reader["CRIANCA_FOTO"].ToString();
                        crianca.count = Convert.ToInt32(reader["CRIANCA_COUNT"]);
                        crianca.combo = reader["CRIANCA_COMBO"].ToString();
                        crianca.status = reader["CRIANCA_STATUS"].ToString();
                    }
                }
                conexao.Close();
            }
            return crianca;
        }

        public void alterarStatusCombo(CriancaBe crianca)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "UPDATE CRIANCA SET CRIANCA_COMBO = @combo WHERE CRIANCA_ID = @id";
                command.Parameters.AddWithValue("@id", crianca.id);
                command.Parameters.AddWithValue("@combo", crianca.combo = "DESATIVO");
               

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        public List<CriancaBe> buscarCriacaBeData(string data)
        {
            List<CriancaBe> ListaCrianca = new List<CriancaBe>();
            try
            {
                using (SqlConnection conexao = Connection.CriarConexao())
                {
                    SqlCommand command = new SqlCommand();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    SqlDataReader reader;

                    command.Connection = conexao;
                    command.CommandText = "SELECT * FROM CRIANCA WHERE CRIANCA_NASCIMENTO = @data;";
                    command.Parameters.AddWithValue("@data", data);
                    conexao.Open();
                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            CriancaBe novaCrianca = new CriancaBe();
                            novaCrianca.id = Convert.ToInt32(reader["CRIANCA_ID"]);
                            novaCrianca.nome = reader["CRIANCA_NOME"].ToString();
                            novaCrianca.nascimento = Convert.ToDateTime(reader["CRIANCA_NASCIMENTO"]);
                            novaCrianca.restricoes = reader["CRIANCA_RESTRICOES"].ToString();
                            novaCrianca.enderecoFoto = "";
                            novaCrianca.count = Convert.ToInt32(reader["CRIANCA_COUNT"]);
                            ListaCrianca.Add(novaCrianca);
                        }
                    }
                    conexao.Close();
                }

            }
            catch{}
               
            return ListaCrianca;
        }

        public void alterarStatusCrianca(int id)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "UPDATE CRIANCA SET CRIANCA_STATUS = @status WHERE CRIANCA_ID = @id";
                command.Parameters.AddWithValue("@id", id);
                command.Parameters.AddWithValue("@status", "DESATIVO");


                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        public void reiniciarContador(int IdCrianca)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "UPDATE CRIANCA SET CRIANCA_COUNT = '1' WHERE CRIANCA_ID = " + IdCrianca;

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        public void incrementarContador(int IdCrianca, int count)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "UPDATE CRIANCA SET CRIANCA_COUNT = '"+ count+"' WHERE CRIANCA_ID = " + IdCrianca;

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }
    }
}

